package com.hongdee.poi;

import com.hongdee.common.util.DateUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.xml.sax.*;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParserFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

public class XLSXReader {
    private static final Log LOG = LogFactory.getLog(XLSXReader.class);
    private static final int READ_ALL = -1;
    private OPCPackage xlsxPackage;
    private XSSFSheetXMLHandler.SheetContentsHandler sheetContentsHandler;
    private ExcelSheetCallback sheetCallback;

    public XLSXReader(OPCPackage pkg, XSSFSheetXMLHandler.SheetContentsHandler sheetContentsHandler, ExcelSheetCallback sheetCallback) {
        this.xlsxPackage = pkg;
        this.sheetContentsHandler = sheetContentsHandler;
        this.sheetCallback = sheetCallback;
    }

    public XLSXReader(String filePath, XSSFSheetXMLHandler.SheetContentsHandler sheetContentsHandler, ExcelSheetCallback sheetCallback)
            throws Exception {
        this(getOPCPackage(getFile(filePath)), sheetContentsHandler, sheetCallback);
    }

    public XLSXReader(File file, XSSFSheetXMLHandler.SheetContentsHandler sheetContentsHandler, ExcelSheetCallback sheetCallback)
            throws Exception {
        this(getOPCPackage(file), sheetContentsHandler, sheetCallback);
    }

    /**
     * 根据输入流构建
     *
     * @param
     * @param sheetContentsHandler
     * @param sheetCallback
     * @throws Exception
     */
    public XLSXReader(InputStream inputStream, XSSFSheetXMLHandler.SheetContentsHandler sheetContentsHandler, ExcelSheetCallback sheetCallback)
            throws Exception {
        this(getOPCPackage(inputStream), sheetContentsHandler, sheetCallback);
    }

    public void process()
            throws Exception {
        read(-1);
    }

    public void process(int sheetNumber)
            throws Exception {
        read(sheetNumber);
    }

    private void read(int sheetNumber) throws RuntimeException {
        try {
            System.out.println("读取ReadOnlySharedStringsTable开始" + DateUtils.getTimeStr());
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
            System.out.println("读取ReadOnlySharedStringsTable结束" + DateUtils.getTimeStr());
            System.out.println("创建xssfReader开始" + DateUtils.getTimeStr());
            XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
            StylesTable styles = xssfReader.getStylesTable();
            System.out.println("创建xssfReader结束" + DateUtils.getTimeStr());
            System.out.println("创建 worksheet 开始" + DateUtils.getTimeStr());
            XSSFReader.SheetIterator worksheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
            System.out.println("创建 workSheet 结束" + DateUtils.getTimeStr());
            for (int sheetIndex = 0; worksheets.hasNext(); ++sheetIndex) {
                InputStream stream = worksheets.next();
                if (this.sheetCallback != null) {
                    this.sheetCallback.startSheet(sheetIndex, worksheets.getSheetName());
                }
                if ((-1 == sheetNumber) || (sheetIndex == sheetNumber)) {
                    readSheet(styles, strings, stream);
                }
                IOUtils.closeQuietly(stream);
                if (this.sheetCallback != null)
                    this.sheetCallback.endSheet();
            }
        } catch (IOException ioe) {
            LOG.error(ioe.getMessage(), ioe.getCause());
        } catch (SAXException se) {
            LOG.error(se.getMessage(), se.getCause());
        } catch (OpenXML4JException oxe) {
            LOG.error(oxe.getMessage(), oxe.getCause());
        } catch (ParserConfigurationException pce) {
            LOG.error(pce.getMessage(), pce.getCause());
        }
    }

    private void readSheet(StylesTable styles, ReadOnlySharedStringsTable sharedStringsTable, InputStream sheetInputStream)
            throws IOException, ParserConfigurationException, SAXException {
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        XMLReader sheetParser = saxFactory.newSAXParser().getXMLReader();

        /**
         * 为了Excel文件中的日期格式处理，统一输出格式
         */
        ContentHandler handler =
                new XSSFSheetXMLHandler(styles, sharedStringsTable, this.sheetContentsHandler, true) {
                    boolean dateFlag = false;
                    String cellRef = null;
                    StringBuffer value = new StringBuffer();

                    @Override
                    public void startElement(String uri, String localName, String name,

                                             Attributes attributes) throws SAXException {
                        //单元格
                        if ("c".equals(name) && null == attributes.getValue("t")) {
                            String cellStyleStr = attributes.getValue("s");
                            try {
                                int styleIndex = Integer.parseInt(cellStyleStr);
                                XSSFCellStyle style = styles.getStyleAt(styleIndex);
                                short formatIndex = style.getDataFormat();
                                String formatString = style.getDataFormatString();
                                if (DateUtil.isADateFormat(formatIndex, formatString)) {
                                    dateFlag = true;
                                    cellRef = attributes.getValue("r");
                                    value.setLength(0);
                                }
                            } catch (java.lang.NumberFormatException e) {
                                // 捕捉数字化异常，不处理
                            }
                        }

                        super.startElement(uri, localName, name, attributes);
                    }


                    @Override
                    public void endElement(String arg0, String arg1, String arg2) throws SAXException {
                        if (dateFlag) {
                            dateFlag = false;
                            if (!"".equals(value.toString())) {
                                double dv = Double.valueOf(value.toString());
                                if (dv > 0.0 && DateUtil.isValidExcelDate(dv)) {
                                    Date d = DateUtil.getJavaDate(dv);
                                    System.out.println("this is a date cell:" + d);
                                    if (null != d) {
                                        sheetContentsHandler.cell(cellRef, String.valueOf(d.getTime()));
                                        return;
                                    }
                                }
                            }

                        }
                        super.endElement(arg0, arg1, arg2);
                    }

                    @Override
                    public void characters(char[] ch, int start, int length)
                            throws SAXException {

                        if (dateFlag) {
                            value.append(ch, start, length);
                        }

                        super.characters(ch, start, length);
                    }

                };

        sheetParser.setContentHandler(handler);
        sheetParser.parse(new InputSource(sheetInputStream));
    }

    private static File getFile(String filePath) throws Exception {
        if ((filePath == null) || (filePath.isEmpty())) {
            throw new Exception("File path cannot be null");
        }

        return new File(filePath);
    }

    private static OPCPackage getOPCPackage(File file) throws Exception {
        if ((file == null) || (!(file.canRead()))) {
            throw new Exception("File object is null or cannot have read permission");
        }

        return OPCPackage.open(new FileInputStream(file));
    }

    /***
     * 根据输入流
     * @param inputStream
     * @return
     * @throws Exception
     */
    private static OPCPackage getOPCPackage(InputStream inputStream) throws Exception {
        if (null == inputStream) {
            throw new Exception("File object is null or cannot have read permission");
        }
        return OPCPackage.open(inputStream);
    }
}

/* Location:           D:\Documents\Downloads\classes\classes\
 * Qualified Name:     com.myjeeva.poi.XLSXReader
 * JD-Core Version:    0.5.3
 */